﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Security.Cryptography;

namespace GestorDeFlotas.Core
{
    public class Usuario
    {
        public string Username { get; set; }

        public byte[] Password { get; private set; }

        public int IntentosLogin { get; set; }

        public bool Activo { get; set; }

        public List<int> Roles { get; private set; }

        public Usuario()
        {
            this.Roles = new List<int>();
        }

        public Usuario(string username)
            : this()
        {
            this.Username = username;
        }

        public Usuario Cargar()
        {
            CargarDatosUsuario();
            CargarRoles();

            return this;
        }

        public void SetPassword(string textPassword)
        {
            this.Password = Encriptar(textPassword);
        }

        public void Crear()
        {
            using (SqlConnection connection = SqlUtils.GetConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();

                try
                {
                    CrearUsuario(connection, transaction);
                    AgregarRoles(connection, transaction);
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw new Exception(string.Format("El usuario {0} ya existe.", this.Username));
                }
            }
        }

        public void Modificar()
        {
            using (SqlConnection connection = SqlUtils.GetConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();

                try
                {
                    ModificarDatosUsuario(connection, transaction);
                    ModificarRoles(connection, transaction);
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public void ResetearIntentosLogin()
        {
            this.IntentosLogin = 0;
            ActualizarIntentosLogin();
        }

        public void IncrementarIntentosLogin()
        {
            this.IntentosLogin++;
            ActualizarIntentosLogin();
        }

        public void SetInactivo()
        {
            using (SqlConnection connection = SqlUtils.GetConnection())
            {
                string sql = "UPDATE [CRAZY_TAXI].[TL_Usuarios] SET usu_activo = 0 WHERE usu_username = @Username";

                SqlCommand updateRecord = SqlUtils.GetNewCommand(sql);

                SqlParameter rowParameter = new SqlParameter("@Username", SqlDbType.VarChar);
                rowParameter.IsNullable = false;
                rowParameter.Value = this.Username;

                updateRecord.Parameters.Add(rowParameter);
                updateRecord.Connection = connection;
                updateRecord.Connection.Open();
                updateRecord.ExecuteNonQuery();
            }
        }

        public IEnumerable<int> GetFuncionalidades()
        {
            List<int> funcionalidades = new List<int>();

            using (SqlConnection connection = SqlUtils.GetConnection())
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT DISTINCT fxrfun_codigo FROM [CRAZY_TAXI].[TL_FuncionalidadesPorRol] ");
                sql.Append("INNER JOIN [CRAZY_TAXI].[TL_RolesPorUsuario] ON fxrrol_codigo = rxurol_codigo ");
                sql.Append("WHERE rxuusu_username = @Username");

                SqlCommand getFuncionalidades = SqlUtils.GetNewCommand(sql.ToString());

                SqlParameter rowParameter = new SqlParameter("@Username", SqlDbType.VarChar);
                rowParameter.IsNullable = false;
                rowParameter.Value = this.Username;

                getFuncionalidades.Parameters.Add(rowParameter);
                getFuncionalidades.Connection = connection;
                getFuncionalidades.Connection.Open();
                SqlDataReader reader = getFuncionalidades.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        funcionalidades.Add(reader.GetInt32(0));
                    }
                }
            }

            return funcionalidades;
        }

        public bool ValidarPassword(string inputTextPassword)
        {
            return this.Password.SequenceEqual(Encriptar(inputTextPassword));
        }

        private byte[] Encriptar(string textPassword)
        {
            SHA256 shaM = new SHA256Managed();
            return shaM.ComputeHash(Encoding.UTF8.GetBytes(textPassword));
        }

        private void ModificarDatosUsuario(SqlConnection connection, SqlTransaction transaction)
        {
            string sql = "UPDATE [CRAZY_TAXI].[TL_Usuarios] SET [usu_password] = @Password, [usu_activo] = @Activo WHERE [usu_username] = @Username";
            SqlCommand modificarRol = SqlUtils.GetNewCommand(sql);

            SqlParameter paramPassword = new SqlParameter("@Password", SqlDbType.VarBinary, this.Password.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, this.Password);

            SqlParameter paramActivo = new SqlParameter("@Activo", SqlDbType.Bit);
            paramActivo.IsNullable = false;
            paramActivo.Value = this.Activo ? 1 : 0;

            SqlParameter paramUsername = new SqlParameter("@Username", SqlDbType.VarChar);
            paramUsername.IsNullable = false;
            paramUsername.Value = this.Username;

            modificarRol.Parameters.Add(paramPassword);
            modificarRol.Parameters.Add(paramActivo);
            modificarRol.Parameters.Add(paramUsername);
            modificarRol.Connection = connection;
            modificarRol.Transaction = transaction;
            modificarRol.ExecuteNonQuery();
        }

        private void ActualizarIntentosLogin()
        {
            using (SqlConnection connection = SqlUtils.GetConnection())
            {
                string sql = "UPDATE [CRAZY_TAXI].[TL_Usuarios] SET usu_intentos = @Intentos WHERE usu_username = @Username";

                SqlCommand updateRecord = SqlUtils.GetNewCommand(sql);

                SqlParameter intentos = new SqlParameter("@Intentos", SqlDbType.Int);
                intentos.IsNullable = false;
                intentos.Value = this.IntentosLogin;
                SqlParameter username = new SqlParameter("@Username", SqlDbType.VarChar);
                username.IsNullable = false;
                username.Value = this.Username;

                updateRecord.Parameters.Add(intentos);
                updateRecord.Parameters.Add(username);
                updateRecord.Connection = connection;
                updateRecord.Connection.Open();
                updateRecord.ExecuteNonQuery();
            }
        }

        private void ModificarRoles(SqlConnection connection, SqlTransaction transaction)
        {
            string sql = "DELETE FROM [CRAZY_TAXI].[TL_RolesPorUsuario] WHERE [rxuusu_username] = @Username";
            SqlCommand eliminarFuncionalidades = SqlUtils.GetNewCommand(sql.ToString());

            SqlParameter paramUsername = new SqlParameter("@Username", SqlDbType.VarChar);
            paramUsername.IsNullable = false;
            paramUsername.Value = this.Username;

            eliminarFuncionalidades.Parameters.Add(paramUsername);
            eliminarFuncionalidades.Connection = connection;
            eliminarFuncionalidades.Transaction = transaction;
            eliminarFuncionalidades.ExecuteNonQuery();

            this.AgregarRoles(connection, transaction);
        }

        private void CargarDatosUsuario()
        {
            using (SqlConnection connection = SqlUtils.GetConnection())
            {
                string sql = "SELECT [usu_password], [usu_intentos], [usu_activo] FROM [CRAZY_TAXI].[TL_Usuarios] WHERE [usu_username] = @Username";

                SqlCommand getUsuario = SqlUtils.GetNewCommand(sql);

                SqlParameter rowParameter = new SqlParameter("@Username", SqlDbType.VarChar);
                rowParameter.IsNullable = false;
                rowParameter.Value = this.Username;

                getUsuario.Parameters.Add(rowParameter);
                getUsuario.Connection = connection;
                getUsuario.Connection.Open();
                SqlDataReader reader = getUsuario.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();

                    byte[] password = new byte[32];
                    reader.GetBytes(0, 0, password, 0, 32);
                    this.Password = password;
                    this.IntentosLogin = reader.GetInt32(1);
                    this.Activo = reader.GetBoolean(2);
                }
                else
                {
                    throw new Exception(string.Format("No existe un usuario con username {0}.", this.Username));
                }

                reader.Close();
            }
        }

        private void CargarRoles()
        {
            using (SqlConnection connection = SqlUtils.GetConnection())
            {
                string sql = "SELECT [rxurol_codigo] FROM [CRAZY_TAXI].[TL_RolesPorUsuario] WHERE [rxuusu_username] = @Username";
                SqlCommand getRoles = SqlUtils.GetNewCommand(sql);
                
                SqlParameter rowParameter = new SqlParameter("@Username", SqlDbType.VarChar);
                rowParameter.IsNullable = false;
                rowParameter.Value = this.Username;

                getRoles.Parameters.Add(rowParameter);
                getRoles.Connection = connection;
                getRoles.Connection.Open();
                SqlDataReader reader = getRoles.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        this.Roles.Add(reader.GetInt32(0));
                    }
                }

                reader.Close();
            }
        }

        private void CrearUsuario(SqlConnection connection, SqlTransaction transaction)
        {
            string sql = "INSERT INTO [CRAZY_TAXI].[TL_Usuarios] (usu_username, usu_password, usu_activo) VALUES (@Username, @Password, @Activo)";
            SqlCommand crearRol = SqlUtils.GetNewCommand(sql);

            SqlParameter paramNombre = new SqlParameter("@Username", SqlDbType.VarChar);
            paramNombre.IsNullable = false;
            paramNombre.Value = this.Username;

            SqlParameter paramPassword = new SqlParameter("@Password", SqlDbType.VarBinary, this.Password.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, this.Password);

            SqlParameter paramActivo = new SqlParameter("@Activo", SqlDbType.Bit);
            paramActivo.IsNullable = false;
            paramActivo.Value = 1;

            crearRol.Parameters.Add(paramNombre);
            crearRol.Parameters.Add(paramPassword);
            crearRol.Parameters.Add(paramActivo);
            crearRol.Connection = connection;
            crearRol.Transaction = transaction;
            crearRol.ExecuteNonQuery();
        }

        private void AgregarRoles(SqlConnection connection, SqlTransaction transaction)
        {
            if (this.Roles.Count > 0)
            {
                StringBuilder sql = new StringBuilder();
                sql.AppendFormat("INSERT INTO [CRAZY_TAXI].[TL_RolesPorUsuario]");

                foreach (int rol in this.Roles)
                {
                    sql.AppendFormat(" SELECT {0}, '{1}' UNION ALL", rol, this.Username);
                }

                sql.Remove(sql.ToString().LastIndexOf(" UNION ALL"), " UNION ALL".Length);

                SqlCommand agregarFuncionalidades = SqlUtils.GetNewCommand(sql.ToString());
                agregarFuncionalidades.Connection = connection;
                agregarFuncionalidades.Transaction = transaction;
                agregarFuncionalidades.ExecuteNonQuery();
            }            
        }
    }
}
